# _*_ coding:utf-8 _*_
# @File  : strategy.py
# @Time  : 2022-02-08  08:00
# @Author: zizle


# 投顾策略
import datetime
import pandas as pd
import itertools
from typing import Optional
from fastapi import APIRouter, Query, Body, Header
from fastapi.encoders import jsonable_encoder
from pydantic import BaseModel, ValidationError, validator, conint
from hutool import security
from v1_all_api import constant
from v1_all_api.all_response import AllResponse
from v1_all_api.all_utils import datetime_utils
from global_constant import VARIETY
from db import FAConnection, utils as dbutils
from . import work_utils

work_strategy_api = APIRouter()


def format_strategy_response(data_list):  # 格式化策略数据
    response_data = []
    for item in data_list:
        detail = eval(item['strategy'])

        # 转换数据类型
        p_num = 4 if item['variety_en'] in VARIETY.VARIETY_NAMES.keys() else 2
        sp, tp, cp = item['strategy_price'], item['target_price'], item['cutloss_price']
        sp = int(sp) if int(sp) == round(sp, p_num) else round(sp, p_num)
        tp = int(tp) if int(tp) == round(tp, p_num) else round(tp, p_num)
        cp = int(cp) if int(cp) == round(cp, p_num) else round(cp, p_num)

        # 根据type显示策略文字内容
        if item['type'] == 1:  # 单边策略
            detail = detail[0]
            v_name = ''
            if item['variety_en'] in VARIETY.VARIETY_NAMES.keys():
                v_name = VARIETY.VARIETY_NAMES.get(item['variety_en'])
            direction = '做多' if detail['direction'] == 'B' else '做空'
            strategy = f'【交易策略】建议于{sp}{detail["price_unit"]}附近{direction}{v_name}{detail["contract"]}，' \
                       f'目标{tp}{detail["price_unit"]}，止损{cp}{detail["price_unit"]}。' \
                       f'{item["description"]}'
            hands_text = detail['hands']
        elif item['type'] == 2:  # 双品种套利策略
            v1_data, v2_data = detail
            d1 = '做多' if v1_data["direction"] == 'B' else '做空'
            d2 = '做多' if v2_data['direction'] == 'B' else '做空'
            v1_name = v2_name = ''
            if v1_data['variety_en'] in VARIETY.VARIETY_NAMES.keys():
                v1_name = VARIETY.VARIETY_NAMES.get(v1_data['variety_en'])
                v2_name = VARIETY.VARIETY_NAMES.get(v2_data['variety_en'])

            # 组织数据
            strategy = f'【套利策略】{v1_name}{v1_data["contract"]}与{v2_name}{v2_data["contract"]}合约价差为{sp}时，' \
                       f'建议{d1}{v1_name}{v1_data["contract"]},{d2}{v2_name}{v2_data["contract"]}，' \
                       f'价差为{tp}附近止盈，止损价差为{cp}附近。{item["description"]}'

            hands_text = v1_data['hands']
        elif item['type'] == 3:  # 多品种套利策略
            strategy = '【套利策略】建议同时'
            for stv_item in detail:  # stv= strategy_variety
                d = '做多' if stv_item['direction'] == 'B' else '做空'
                v_name = VARIETY.VARIETY_NAMES.get(stv_item['variety_en'], '')
                strategy += f'{d}{v_name}{stv_item["contract"]},数量:{stv_item["hands"]}手;'
            strategy = strategy[:-1] + '。'
            strategy += item['description']
            hands_text = ''
        else:
            continue
        can_delete = True if (datetime.datetime.now() - item['create_time']).seconds <= 1800 else False
        open_time = item['open_time'].strftime('%Y-%m-%d %H:%M:%S') if item['open_time'] else ''
        close_time = item['close_time'].strftime('%Y-%m-%d %H:%M:%S') if item['close_time'] else ''

        profit = item['profit']
        profit = int(profit) if int(profit) == round(profit, 2) else round(profit, 2)
        is_running = False if close_time else True
        examine_time = item['examine_time'].strftime('%Y-%m-%d %H:%M:%S') if item.get('examine_time') else '-'
        delete_time = item['delete_time'].strftime('%Y-%m-%d %H:%M:%S') if item.get('delete_time') else '-'
        response_data.append({
            'id': item['id'], 'type': item['type'], 'create_time': item['create_time'].strftime('%Y-%m-%d %H:%M:%S'),
            'open_time': open_time, 'close_time': close_time, 'profit': profit, 'close_way': item['close_way'],
            'variety_name': VARIETY.VARIETY_NAMES.get(item['variety_en']), 'can_delete': can_delete,
            'content': strategy, 'hands': hands_text,
            'admin_name': item.get('admin_name'), 'is_running': is_running,
            'is_delete': item.get('is_delete'), 'examine_state': item.get('examine_state'),
            'examiner': item.get('examiner'),
            'examine_time': examine_time,
            'delete_time': delete_time
        })
    return response_data


class StrategyItem(BaseModel):
    type: int
    ykb: float
    variety_en: str
    description: str
    fund: float
    strategy: str
    strategy_price: float
    target_price: float
    cutloss_price: float
    is_open: int = 1


@work_strategy_api.get('/')  # 查询交易策略，按日期范围+分页
def query_strategy(ruizy_token: str = Header(...), start: str = Query(...), end: str = Query(...),
                   page: int = Query(1), page_size: int = Query(50), u: int = Query(0)):
    person = security.decrypt_access_token(ruizy_token)
    if not person:
        return AllResponse.no_authorization()
    if not security.operator_is_manager(person):
        return AllResponse.forbidden_error()
    start = datetime_utils.auth_datetime_string(start, f='%Y%m%d', rf='%Y-%m-%d')
    end = datetime_utils.auth_datetime_string(end, f='%Y%m%d', rf='%Y-%m-%d')
    if not all([start, end]):
        return AllResponse.validate_error(msg='format start and end date error.')
    start_date = start + '00:00:00'
    end_date = end + ' 23:59:59'
    creator_id = u if u else 0
    sql = """
        SELECT a.id,a.create_time,a.type,a.variety_en,b.admin_name,
        a.description,a.open_time,a.close_time,a.close_way,a.profit,a.fund,a.strategy_price,a.target_price,
        a.cutloss_price,a.strategy,a.examine_time,a.examine_state,a.examiner,a.is_delete,a.delete_time 
        FROM info_strategy AS a INNER JOIN ruizy_user AS b ON a.creator=b.id 
        WHERE a.create_time>=%s AND a.create_time<=%s AND IF('0'=%s,TRUE,a.creator=%s) 
        ORDER BY IF(ISNULL(a.close_time),0,1), a.create_time DESC;
    """
    db_conn = FAConnection()
    ret = db_conn.query_paginator(sql, param=[start_date, end_date, str(creator_id), creator_id],
                                  page=page, page_size=page_size)
    ret['data'] = format_strategy_response(ret['data'])
    return AllResponse.operate_successfully(data=ret)


@work_strategy_api.post('/')  # 发布交易策略
def publish_strategy(ruizy_token: str = Header(...), strategy_item: StrategyItem = Body(...)):
    person = security.decrypt_access_token(ruizy_token)
    if not person:
        return AllResponse.no_authorization()

    is_validate = strategy_item.variety_en in constant.SPECIAL_VARIETY.keys()  # 外汇等特殊品种不统计

    if not is_validate:
        if strategy_item.type in [1, 2]:
            # 计算盈亏比
            yl = strategy_item.target_price - strategy_item.strategy_price
            ks = strategy_item.cutloss_price - strategy_item.strategy_price
            if abs(yl / ks) < 2:
                return AllResponse.validate_error(msg='策略盈亏比需大于2:1。')
            if strategy_item.variety_en not in VARIETY.EXCLUDE_PRICE_INTERVAL:
                # 检测价格区间
                if strategy_item.target_price != 0:
                    if abs(round(strategy_item.strategy_price / strategy_item.target_price - 1, 2)) < 0.02:
                        return AllResponse.validate_error(msg='价格区间需为2%以上。')
        else:
            if strategy_item.ykb < 2:
                return AllResponse.validate_error(msg='策略盈亏比需大于2:1。')

    if len(strategy_item.description.strip()) < 1:
        return AllResponse.validate_error(msg='请填写策略逻辑理由...')

    is_open = 1 if strategy_item.is_open else 0
    json_data = jsonable_encoder(strategy_item)
    # 处理数据保存入库
    json_data['creator'] = person['uid']
    json_data['is_open'] = is_open

    save_sql = """
        INSERT INTO info_strategy (creator,type,variety_en,description,fund,strategy_price,target_price,
        cutloss_price,strategy,is_open,is_ruizhi) 
        VALUES (%(creator)s,%(type)s,%(variety_en)s,%(description)s,%(fund)s,%(strategy_price)s,
        %(target_price)s,%(cutloss_price)s,%(strategy)s,%(is_open)s,%(is_ruizhi)s);
    """
    with FAConnection() as cursor:
        # 查询是否为对外系统
        cursor.execute("""
            SELECT id,user_id,variety_en,expire_date FROM info_strategy_ruizhi_config 
            WHERE user_id=%s OR variety_en=%s;
        """, [person['uid'], json_data['variety_en']])
        config = cursor.fetchall()
        # 规则
        is_ruizhi = 0
        for c in config:  # 用户有配置表示通过，可对外
            if c['user_id'] == person['uid'] and c['expire_date'] >= datetime.date.today():
                is_ruizhi = 1
                break
        for c in config:  # 但品种配置了，并且有效期已过。表示不可对外
            if c['variety_en'] == json_data['variety_en'] and c['expire_date'] < datetime.date.today():
                is_ruizhi = 0
                break
        json_data['is_ruizhi'] = is_ruizhi
        success = cursor.execute(save_sql, json_data)
    return AllResponse.operate_successfully() if success else AllResponse.server_error()


@work_strategy_api.delete('/')  # 删除交易策略
def delete_strategy_single(ruizy_token: str = Header(...), sid: int = Query(...)):
    person = security.decrypt_access_token(ruizy_token)
    if not person:
        return AllResponse.no_authorization()
    # is_admin = security.operator_is_manager(person)
    # 查询策略进行验证
    db_conn = FAConnection()
    exist = db_conn.query('SELECT id,create_time,open_time FROM info_strategy WHERE id=%s LIMIT 1;', param=[sid],
                          fetchone=True, keep_conn=True)[0]
    if not exist or (datetime.datetime.now() - exist['create_time']).seconds > 1800:
        db_conn.close()
        return AllResponse.validate_error(msg='策略创建超过30分钟，删除失败了..')
    if exist['open_time']:
        return AllResponse.validate_error(msg='策略已建仓不能删除!')

    # db_conn = FAConnection()
    # if not is_admin:  # 不是管理员,查询策略记录进行验证
    #     exist = db_conn.query('SELECT id,create_time FROM info_strategy WHERE id=%s LIMIT 1;', param=[sid],
    #                           fetchone=True, keep_conn=True)[0]
    #     if not exist or (datetime.datetime.now() - exist['create_time']).seconds > 1800:
    #         db_conn.close()
    #         return AllResponse.validate_error(msg='策略创建超过30分钟,无法删除!')

    # 标记删除
    sql = "UPDATE info_strategy SET is_delete=1,deleter_id=%s,delete_time=NOW() WHERE id=%s LIMIT 1;"
    _, success = db_conn.execute(sql, param=[person['uid'], sid])
    return AllResponse.operate_successfully() if success else AllResponse.server_error()


class PutStrategyItem(BaseModel):
    sid: int
    examine_state: int


@work_strategy_api.put('/examine/')  # 修改交易策略
def modify_strategy(ruizy_token: str = Header(...), examine_item: PutStrategyItem = Body(...)):
    person = security.decrypt_access_token(ruizy_token)
    if not person:
        return AllResponse.no_authorization()
    # 查询用户
    db_conn = FAConnection()
    op_user = db_conn.query("SELECT id,admin_name FROM ruizy_user WHERE id=%s LIMIT 1;", param=[person['uid']],
                            fetchone=True, keep_conn=True)[0]
    if not op_user:
        db_conn.close()
        return AllResponse.validate_error(msg='用户不存在！')
    # 修改数据
    sql = "UPDATE info_strategy SET examine_time=%s,examine_state=%s,examiner=%s WHERE id=%s LIMIT 1;"
    prm = (datetime_utils.today(f='%Y-%m-%d %H:%M:%S'), examine_item.examine_state,
           op_user['admin_name'], examine_item.sid)
    _, success = db_conn.execute(sql, param=prm)
    return AllResponse.operate_successfully() if success else AllResponse.server_error()


@work_strategy_api.get('/my/')  # 获取个人的策略数据
def my_strategy(ruizy_token: str = Header(...), page: int = Query(1, ge=1), page_size: int = Query(8, ge=1)):
    person = security.decrypt_access_token(ruizy_token)
    if not person:
        return AllResponse.no_authorization()

    start, offset = (page - 1) * page_size, page_size

    query_sql = 'SELECT SQL_CALC_FOUND_ROWS a.id,a.create_time,a.type,a.variety_en,b.variety_name,' \
                'a.description,a.open_time,a.close_time,a.close_way,a.profit,a.fund,a.strategy_price,a.target_price,' \
                'a.cutloss_price,a.strategy,a.examine_state,a.examiner ' \
                'FROM info_strategy AS a INNER JOIN ruizy_variety AS b ON a.variety_en=b.variety_en ' \
                'WHERE a.is_delete=0 AND a.creator=%s ' \
                'ORDER BY IF(ISNULL(a.close_time),0,1), a.create_time DESC LIMIT %s,%s;'

    db = FAConnection(conn_name='查询个人交易策略')
    records = db.query(query_sql, param=[person['uid'], start, offset], keep_conn=True)

    total_obj = db.query('SELECT FOUND_ROWS() AS total;', fetchone=True)[0]
    total_count = total_obj['total'] if total_obj else 1  # 当前数量
    # total_page = (total_count + page_size - 1) // page_size

    response_data = {
        'data': format_strategy_response(records),
        'page': page,
        'page_size': page_size,
        'total_count': total_count
    }
    return AllResponse.operate_successfully(data=response_data)


@work_strategy_api.put('/my/open/')  # 策略开仓
def modify_strategy(ruizy_token: str = Header(...), strategy_id: int = Body(..., ge=1, embed=True)):
    person = security.decrypt_access_token(ruizy_token)
    if not person:
        return AllResponse.no_authorization()

    # 将数据的开仓时间设置为现在（未开仓的才能开仓）
    sql = 'UPDATE info_strategy SET open_time=%s WHERE id=%s AND creator=%s AND open_time IS NULL;'
    db = FAConnection(conn_name='策略开仓')
    _, s = db.execute(sql, param=[datetime.datetime.now(), strategy_id, person['uid']], keep_conn=True)
    if s:
        # 查询策略，返回策略信息
        query_sql = 'SELECT a.id,a.create_time,a.type,a.variety_en,b.variety_name,a.description,a.open_time,' \
                    'a.close_time,a.close_way,a.profit,a.fund,a.strategy_price,a.target_price,a.cutloss_price,' \
                    'a.strategy,a.examine_state ' \
                    'FROM info_strategy AS a INNER JOIN ruizy_variety AS b ON a.variety_en=b.variety_en ' \
                    'WHERE a.id=%s AND a.creator=%s;'
        s_item = db.query(query_sql, param=[strategy_id, person['uid']], fetchone=True)
        response_data = format_strategy_response(s_item)[0]
        response_data['examine_state'] = s_item[0]['examine_state']
        return AllResponse.operate_successfully(data=response_data, msg='设置策略为开仓状态成功!')
    else:
        db.close()
        return AllResponse.server_error()


class CloseBodyItem(BaseModel):
    strategy_id: int
    close_flag: str
    profit: float


@work_strategy_api.put('/my/close/')  # '策略平仓'
def open_strategy(ruizy_token: str = Header(...), body_item: CloseBodyItem = Body(...)):
    if body_item.close_flag not in ['target', 'cutloss', 'forced']:
        return AllResponse.validate_error()
    person = security.decrypt_access_token(ruizy_token)
    if not person:
        return AllResponse.no_authorization()

    # 查询出数据
    # 查询策略，返回策略信息
    query_sql = 'SELECT a.id,a.create_time,a.type,a.variety_en,b.variety_name,a.description,a.open_time,' \
                'a.close_time,a.profit,a.fund,a.strategy_price,a.target_price,a.cutloss_price,a.strategy ' \
                'FROM info_strategy AS a INNER JOIN ruizy_variety AS b ON a.variety_en=b.variety_en ' \
                'WHERE a.id=%s AND a.creator=%s;'
    db = FAConnection(conn_name='平仓策略')
    s_item = db.query(query_sql, param=[body_item.strategy_id, person['uid']], fetchone=True, keep_conn=True)[0]
    if not s_item:
        return AllResponse.server_no_content()
    if s_item['type'] < 2:
        # 单边策略根据计算结果
        strategy = eval(s_item['strategy'])[0]
        if body_item.close_flag == 'target':  # 触发目标价  profit = |目标价 - 开仓价| * 手数 * 合约乘数
            profit = abs(round(float(s_item['target_price'] - s_item['strategy_price']), 4)) * strategy['hands'] * \
                     strategy['exchange_mul']
        elif body_item.close_flag == 'cutloss':  # 触发止损价
            profit = -abs(round(float(s_item['cutloss_price'] - s_item['strategy_price']), 4)) * strategy['hands'] * \
                     strategy['exchange_mul']
        else:  # 未建仓而平仓
            profit = 0
    else:  # 套利策略直接取用结果
        profit = body_item.profit
    # 更新收益结果和平仓时间
    update_sql = 'UPDATE info_strategy SET close_time=%s,profit=%s,close_way=%s WHERE id=%s;'
    _, s = db.execute(update_sql, param=[datetime.datetime.now(), profit, body_item.close_flag, body_item.strategy_id])
    if s:
        s_item['profit'] = profit
        s_item['close_time'] = datetime.datetime.now()
        s_item['close_way'] = body_item.close_flag
        s_item['examine_state'] = 1
        response_data = format_strategy_response([s_item])[0]
        return AllResponse.operate_successfully(data=response_data, msg='策略结束成功!')
    else:
        return AllResponse.server_error()


@work_strategy_api.get('/my/tj/', summary='个人策略统计')  # 个人策略统计
def get_strategy_tj(ruizy_token: str = Header(...)):
    person = security.decrypt_access_token(ruizy_token)
    if not person:
        return AllResponse.no_authorization()
    person_id = person['uid']
    sql = 'SELECT id,create_time,profit,fund,open_time,close_time ' \
          'FROM info_strategy ' \
          'WHERE creator=%s AND is_delete=0;'
    db = FAConnection(conn_name='查询统计策略')
    records = db.query(sql, param=[person_id])
    record_df = pd.DataFrame(records)
    # 当策略为空时
    if record_df.empty:
        return AllResponse.server_no_content(msg='还没有发布任何策略!')
    # 筛选出未建仓和运行中的数量
    # open_time与close_time都是空的 = 未建仓
    # open_time不为空，close_time为空 = 运行中
    no_build_df = record_df[pd.isna(record_df['open_time']) & pd.isna(record_df['close_time'])]  # 未建仓
    running_df = record_df[~pd.isna(record_df['open_time']) & pd.isna(record_df['close_time'])]  # 运行中
    df = record_df[~pd.isna(record_df['close_time'])].copy()
    nb_count = no_build_df.shape[0]
    running_count = running_df.shape[0]

    current_datetime = datetime.datetime.today()
    cy, cm = current_datetime.year, current_datetime.month
    df['month'] = df['create_time'].apply(lambda x: x.month)
    df['year'] = df['create_time'].apply(lambda x: x.year)
    # 统计的指标有：
    # 数量： 本月数量、本年数量、总数量
    cm_df = df[df['month'] == cm]
    cy_df = df[df['year'] == cy]
    cm_count, cy_count, total_count = cm_df.shape[0], cy_df.shape[0], df.shape[0]
    # 月数据
    if cm_count == 0:
        cm_success = 0
        cm_success_rate = '-'
        cm_fund = 0
        cm_profit = 0
        cm_profit_rate = '-'
    else:
        cm_success = cm_df[cm_df['profit'] > 0].shape[0]
        cm_success_rate = cm_success / cm_count
        cm_fund = cm_df['fund'].sum()
        cm_profit = cm_df['profit'].sum()
        cm_profit_rate = cm_profit / cm_fund
    # 年数据
    if cy_count == 0:
        cy_success = 0
        cy_success_rate = '-'
        cy_fund = 0
        cy_profit = 0
        cy_profit_rate = '-'
    else:
        cy_success = cy_df[cy_df['profit'] > 0].shape[0]
        cy_success_rate = cy_success / cy_count
        cy_fund = cy_df['fund'].sum()
        cy_profit = cy_df['profit'].sum()
        cy_profit_rate = cy_profit / cy_fund
    # 总数据
    if total_count == 0:
        t_success = 0
        t_success_rate = '-'
        t_fund = 0
        t_profit = 0
        t_profit_rate = '-'
    else:
        t_success = df[df['profit'] > 0].shape[0]
        t_success_rate = t_success / total_count
        t_fund = df['fund'].sum()
        t_profit = df['profit'].sum()
        t_profit_rate = t_profit / t_fund

    rate_dict = {
        'cm_success_rate': cm_success_rate, 'cy_success_rate': cy_success_rate, 't_success_rate': t_success_rate,
        'cm_profit_rate': cm_profit_rate, 'cy_profit_rate': cy_profit_rate, 't_profit_rate': t_profit_rate
    }
    for k, v in rate_dict.items():
        if v == '-':
            continue
        if v == int(v):
            rate_dict[k] = str(int(v))
        else:
            rate_dict[k] = str(round(v * 100, 2)) + '%'

    count_dict = {
        'nb_count': nb_count, 'running_count': running_count,
        'cm_count': cm_count, 'cy_count': cy_count, 't_count': total_count,
        'cm_success': cm_success, 'cy_success': cy_success, 't_success': t_success,
        'cm_fund': cm_fund, 'cy_fund': cy_fund, 't_fund': t_fund,
        'cm_profit': cm_profit, 'cy_profit': cy_profit, 't_profit': t_profit
    }

    for k, v in count_dict.items():
        if v == '-':
            continue
        if v == int(v):
            count_dict[k] = str(int(v))
        else:
            count_dict[k] = str(round(v, 2))

    response_data = {**rate_dict, **count_dict}

    return AllResponse.operate_successfully(data=response_data)


@work_strategy_api.get('/tj/count/')  # 按日期范围查询数量和成功率[工作管理系统]
def strategy_count(year: int = Query(..., ge=2000), month: int = Query(None, ge=1, le=12)):
    if month:
        ts, te = datetime_utils.month_start_end(year, month)
    else:
        ts, te = datetime_utils.year_start_end(year)
    query_sql = 'SELECT a.id,a.create_time,a.creator,a.type,a.variety_en,a.close_time,a.profit,' \
                'a.fund,a.is_open,b.admin_name ' \
                'FROM info_strategy As a ' \
                'INNER JOIN ruizy_user AS b ON a.creator=b.id ' \
                'WHERE a.is_delete=0 AND a.create_time>=%s AND a.create_time<=%s;'
    query_prm = [ts, te]
    db_conn = FAConnection()
    df = pd.DataFrame(db_conn.query(query_sql, query_prm))
    if df.empty or df[pd.notnull(df['close_time'])].empty:
        return AllResponse.server_no_content(msg='没有数据...')
    # 去掉未结束的策略
    df = df[pd.notnull(df['close_time'])]
    # 获取成功的策略
    sus_df = df[df['profit'].gt(0)]
    # 人员分组计数
    count_df = df.groupby(by=['creator', 'admin_name'])['admin_name'].count()
    sus_count_df = sus_df.groupby(by=['creator', 'admin_name'])['admin_name'].count()
    sus_count_dict = sus_count_df.to_dict()
    # 人员分组计算收益和资金情况
    fund_df = df.groupby(by=['creator', 'admin_name'], as_index=False)[['profit', 'fund']].sum()
    fund_df['profit'] = fund_df['profit'].astype(float)
    fund_df['fund'] = fund_df['fund'].astype(float)
    fund_dict = {i['admin_name']: (i['profit'], i['fund']) for i in fund_df.to_dict(orient='records')}
    response_data = []
    for k, c in count_df.to_dict().items():
        response_data.append({
            'author': k[1],
            'count': c,
            'success_count': sus_count_dict.get(k, 0),
            'profit': fund_dict.get(k[1])[0],
            'fund': fund_dict.get(k[1])[1]
        })
    return AllResponse.operate_successfully(data=response_data)


@work_strategy_api.get('/tj/monthlyCount/')  # 查询年数据，按每月统计出个人的策略数量[工作管理系统]
def monthly_count(year: int = Query(..., ge=2000)):
    ts, te = datetime_utils.year_start_end(year)
    query_sql = 'SELECT a.id,a.create_time,a.creator,a.type,a.variety_en,a.close_time,a.profit,' \
                'a.fund,a.is_open,b.admin_name ' \
                'FROM info_strategy As a ' \
                'INNER JOIN ruizy_user AS b ON a.creator=b.id ' \
                'WHERE a.is_delete=0 AND a.create_time>=%s AND a.create_time<=%s;'
    query_prm = [ts, te]
    db_conn = FAConnection()
    df = pd.DataFrame(db_conn.query(query_sql, query_prm))
    if df.empty or df[pd.notnull(df['close_time'])].empty:
        return AllResponse.server_no_content(msg='没有数据...')
    df['month'] = df['create_time'].apply(lambda x: x.strftime('%Y-%m'))
    # 去掉未结束的策略
    df = df[pd.notnull(df['close_time'])]
    # 获取成功的策略
    sus_df = df[df['profit'].gt(0)]
    # 人员按月分组计数
    count_df = df.groupby(by=['month', 'creator', 'admin_name'])['admin_name'].count()
    sus_count_df = sus_df.groupby(by=['month', 'creator', 'admin_name'])['admin_name'].count()
    sus_count_dict = sus_count_df.to_dict()
    cache_data = []
    for k, c in count_df.to_dict().items():
        # print(k[0], k[2], '总数:', c, '成功数:', sus_count_dict.get(k, 0))
        cache_data.append({
            'author': k[2],
            'month': k[0],
            'count': c,
            'success_count': sus_count_dict.get(k, 0),
        })
    author_monthly_count = {}
    for item in cache_data:
        if item['author'] in author_monthly_count.keys():
            author_monthly_count[item['author']][item['month']] = item['count']
        else:
            author_monthly_count[item['author']] = {item['month']: item['count']}
    response_data = []
    for k, v in author_monthly_count.items():
        v['year_total'] = sum([c for _, c in v.items()])
        v['username'] = k
        response_data.append(v)
    # print(author_monthly_count)
    return AllResponse.operate_successfully(data=response_data)


@work_strategy_api.get('/tj/successRate/')  # 查询策略的人员成功率，品种成功率，时间成功率曲线
def strategy_success_rate(start: str = Query(None), end: str = Query(None),
                          variety: str = Query(None), author: int = Query(None)):
    start = datetime_utils.auth_datetime_string(start, f='%Y-%m-%d')
    end = datetime_utils.auth_datetime_string(end, f='%Y-%m-%d')
    start = start + ' 00:00:00' if start else '1970-01-01 00:00:00'
    end = end + ' 23:59:59' if end else '1970-01-01 00:00:00'

    variety = variety if variety else '0'
    author = author if author else '0'
    query_sql = 'SELECT a.id,a.variety_en,a.create_time,a.close_time,a.profit,a.fund,a.creator,' \
                'b.admin_name ' \
                'FROM info_strategy As a ' \
                'INNER JOIN ruizy_user As b ON a.creator=b.id ' \
                'WHERE a.is_delete=0 AND ' \
                'IF(%s="1970-01-01 00:00:00",TRUE,a.create_time>=%s) AND ' \
                'IF(%s="1970-01-01 00:00:00",TRUE,a.create_time<=%s) AND ' \
                'IF(%s="0",TRUE,a.variety_en=%s) AND ' \
                'IF(%s="0",TRUE,a.creator=%s);'
    query_prm = [start, start, end, end, variety, variety, author, author]
    db_conn = FAConnection()
    df = pd.DataFrame(db_conn.query(query_sql, query_prm))
    if df.empty or df[pd.notnull(df['close_time'])].empty:
        return AllResponse.server_no_content(msg='没有数据...')
    df['create_date'] = df['create_time'].apply(lambda x: x.strftime('%Y-%m-%d'))
    df = df[pd.notnull(df['close_time'])]  # 去掉运行中的策略
    success_df = df[df['profit'].gt(0)]
    # 1 以作者分组计算数量和比率
    author_df = df.groupby(by=['creator', 'admin_name'])['admin_name'].count()
    author_success_df = success_df.groupby(by=['creator', 'admin_name'])['admin_name'].count()
    author_success_dict = author_success_df.to_dict()
    author_data = []
    for k, c in author_df.to_dict().items():
        author_data.append({
            'author': k[1],
            'count': c,
            'success_count': author_success_dict.get(k, 0)
        })

    # 2 以品种分组计算数量和比率
    variety_df = df.groupby(by=['variety_en'])['variety_en'].count()
    variety_success_df = success_df.groupby(by=['variety_en'])['variety_en'].count()
    variety_success_dict = variety_success_df.to_dict()
    variety_data = []
    for k, c in variety_df.to_dict().items():
        variety_data.append({
            'variety_en': k,
            'variety_name': VARIETY.VARIETY_NAMES.get(k, k),
            'count': c,
            'success_count': variety_success_dict.get(k, 0)
        })

    # 3 以日期分组计算数量和比率(累计成功率)
    date_series = df.groupby(by=['create_date'])['create_date'].count()
    date_success_series = success_df.groupby(by=['create_date'])['create_date'].count()
    # 计算数量和成功数的累计数
    date_df = pd.DataFrame({'create_date': date_series.index, 'count': date_series.values})
    date_df.sort_values(by='create_date')
    date_df['cum_count'] = date_df['count'].cumsum()
    date_success_df = pd.DataFrame({'create_date': date_success_series.index, 'count': date_success_series.values})
    date_success_df['cum_success_count'] = date_success_df['count'].cumsum()
    # 合并DataFrame
    date_mix_df = pd.merge(date_df, date_success_df, on='create_date', how='left')
    date_mix_df.fillna(method='ffill', inplace=True)
    date_mix_df.fillna(0, inplace=True)
    date_data = []
    for item in date_mix_df.to_dict(orient='records'):
        date_data.append({
            'create_date': item['create_date'],
            'count': item['cum_count'],
            'success_count': item['cum_success_count']
        })

    date_data.sort(key=lambda x: x['create_date'])

    # 返回的数据
    response_data = {
        'author': author_data,
        'variety': variety_data,
        'daily': date_data
    }
    return AllResponse.operate_successfully(data=response_data)


@work_strategy_api.get('/recentDaysSuccessRate/')  # 查询策略近x天内的成功率数据
def recently_success_rate(days: int = Query(90)):
    end_date = datetime_utils.today(obj=True)
    start_date = end_date + datetime.timedelta(days=-days)
    title = f'{start_date.strftime("%Y-%m-%d")} 至 {end_date.strftime("%Y-%m-%d")}各人员策略成功率表'
    start_time = datetime_utils.to_string(start_date, f='%Y-%m-%d 00:00:00')
    end_time = datetime_utils.to_string(end_date, f='%Y-%m-%d 23:59:59')
    with FAConnection() as cursor:
        cursor.execute("""
            SELECT a.id,a.profit,a.creator,b.admin_name FROM info_strategy As a 
            INNER JOIN ruizy_user As b ON a.creator=b.id
            WHERE a.is_delete=0 AND a.close_time IS NOT NULL AND a.close_time>=%s AND a.close_time<=%s            
        """, [start_time, end_time])

        ret_list = cursor.fetchall()
    if len(ret_list) < 1:
        return AllResponse.operate_successfully(data={'data': [], 'title': title}, msg='查询期间没有数据!')
    user_dict = {}
    user_names = {}
    for row in ret_list:
        user_dict.setdefault(row['creator'], []).append(row)
        user_names[row['creator']] = row['admin_name']

    data_list = []
    for u, u_rows in user_dict.items():
        success_rate = round(len(list(filter(lambda x: x['profit'] > 0, u_rows))) / len(u_rows), 4)
        _obj = {
            'admin_name': user_names.get(u),
            'total_count': len(u_rows),
            'success_rate': success_rate,
            'success_rate_p': f'{round(success_rate * 100, 2)}%'
        }
        data_list.append(_obj)
    data_list.sort(key=lambda x: x['total_count'], reverse=True)

    return AllResponse.operate_successfully(data={'data': data_list, 'title': title})


class StrategyProductItem(BaseModel):
    name: str
    fund: float


@work_strategy_api.post('/product/')  # 添加一个策略产品
def create_strategy_product(ruizy_token: str = Header(...), product_item: StrategyProductItem = Body(...)):
    person = security.decrypt_access_token(ruizy_token)
    if not person:
        return AllResponse.no_authorization()
    db_conn = FAConnection()
    # 查询产品名称是否存在
    exist_sql = """
        SELECT id FROM info_strategy_product WHERE name=%s LIMIT 1;
    """
    name_exist = db_conn.query(exist_sql, param=[product_item.name], fetchone=True, keep_conn=True)[0]
    if name_exist:
        db_conn.close()
        return AllResponse.validate_error(msg='产品名称已存在!')
    # 添加名称
    insert_sql = """
        INSERT INTO info_strategy_product (name,fund,creator) VALUES (%s,%s,%s);
    """
    success, _ = db_conn.insert(insert_sql, param=(product_item.name, product_item.fund, person['uid']))
    return AllResponse.create_successfully() if success else AllResponse.server_error()


@work_strategy_api.get('/product/')  # 查询所有策略产品
def strategy_product():
    sql = """
        SELECT id,name FROM info_strategy_product WHERE is_active=1 ORDER BY id DESC;
    """
    db_conn = FAConnection()
    records = db_conn.query(sql)
    return AllResponse.operate_successfully(data=records)


@work_strategy_api.get('/product/strategy/')  # 查询在/不在产品下的策略
def product_strategy(isin: int = Query(..., ge=0, le=1), p: int = Query(..., ge=1),
                     page: int = Query(1), page_size: int = Query(30)):
    start_index = (page - 1) * page_size
    if isin:
        sql = """
            SELECT SQL_CALC_FOUND_ROWS a.*,b.variety_name,u.admin_name 
            FROM info_strategy As a INNER JOIN ruizy_variety As b ON a.variety_en=b.variety_en 
            INNER JOIN info_strategy_product_relation As c ON a.id=c.strategy_id 
            INNER JOIN ruizy_user As u ON a.creator=u.id 
            WHERE a.is_delete=0 AND c.product_id=%s AND c.is_active=1 
            ORDER BY a.create_time DESC LIMIT %s,%s;
        """
        prm = (p, start_index, page_size)
    else:
        sql = """
            SELECT SQL_CALC_FOUND_ROWS a.*,b.variety_name,u.admin_name 
            FROM info_strategy As a INNER JOIN ruizy_variety As b ON a.variety_en=b.variety_en 
            INNER JOIN ruizy_user As u ON a.creator=u.id 
            WHERE a.is_delete=0 
            AND a.id NOT IN (SELECT strategy_id FROM info_strategy_product_relation WHERE product_id=%s AND is_active=1) 
            ORDER BY a.create_time DESC LIMIT %s,%s;
        """
        prm = (p, start_index, page_size)
    db_conn = FAConnection()
    ret = db_conn.paginator_query(sql, param=prm, page=page, page_size=page_size)
    data_list = format_strategy_response(ret['records'])
    response_data = {
        'page': ret['cur_page'],
        'page_size': page_size,
        'total_count': ret['total_count'],
        'data': data_list
    }
    return AllResponse.operate_successfully(data=response_data)


class ProductStrategyItem(BaseModel):
    action: str
    time_start: Optional[str]
    time_end: Optional[str]
    creator_id: Optional[conint(ge=1)]
    strategy_id: Optional[conint(ge=1)]
    product_id: conint(ge=1)

    @validator('action')
    def validate_action(cls, add_type):
        if add_type not in ['author_add', 'author_remove', 'time_add', 'time_remove',
                            'author_time_add', 'author_time_remove', 'target_add', 'target_remove']:
            raise ValidationError('add_type参数错误')
        return add_type

    @validator('time_start')
    def validate_time_start(cls, time_start):
        if time_start and not datetime_utils.auth_datetime_string(time_start, f='%Y-%m-%d'):
            raise ValidationError('time_start格式错误')
        return time_start

    @validator('time_end')
    def validate_time_end(cls, time_end):
        if time_end and not datetime_utils.auth_datetime_string(time_end, f='%Y-%m-%d'):
            raise ValidationError('time_end')
        return time_end


@work_strategy_api.post('/product/addStrategy/')  # 策略产品内添加策略
def strategy_to_product(ruizy_token: str = Header(...), product: ProductStrategyItem = Body(...)):
    # tips此接口代码可抽取公共部分，此处没做优化
    person = security.decrypt_access_token(ruizy_token)
    if not person:
        return AllResponse.validate_error()
    db_conn = FAConnection()

    def update_relation(relations, keep_conn=False):
        u_sql = """
            UPDATE info_strategy_product_relation SET is_active=%(is_active)s 
            WHERE strategy_id=%(strategy_id)s AND product_id=%(product_id)s;
        """
        c, _ = db_conn.execute(u_sql, param=relations, many=True, keep_conn=keep_conn)
        return c

    def insert_relation(relations, keep_conn=False):
        i_sql = """
            INSERT IGNORE INTO info_strategy_product_relation (strategy_id,product_id,is_active) 
            VALUES (%(strategy_id)s,%(product_id)s,%(is_active)s);
        """
        c, _ = db_conn.insert(i_sql, param=relations, many=True, keep_conn=keep_conn)
        return c

    if product.action == 'author_add':  # 按用户添加
        if not product.creator_id:
            db_conn.close()
            return AllResponse.validate_error(msg='请指定用户再添加!')
        # 查询该用户的所有策略
        sql = """SELECT id FROM info_strategy WHERE creator=%s AND is_delete=0;"""
        records = db_conn.query(sql, param=(product.creator_id,), keep_conn=True)
        relation_list = [{'strategy_id': s['id'], 'product_id': product.product_id, 'is_active': 1} for s in records]
        # 更新数据，再插入数据
        c1 = update_relation(relation_list, keep_conn=True)
        c2 = insert_relation(relation_list)
        count = max(c1, c2)
        return AllResponse.operate_successfully(msg='添加成功,新增数:{}'.format(count))

    elif product.action == 'author_remove':  # 按用户移除
        if not product.creator_id:
            return AllResponse.validate_error(msg='请指定用户再移除!')
        # 查询该用户的所有策略
        sql = """SELECT id FROM info_strategy WHERE creator=%s AND is_delete=0;"""
        records = db_conn.query(sql, param=(product.creator_id,), keep_conn=True)
        relation_list = [{'strategy_id': s['id'], 'product_id': product.product_id, 'is_active': 0} for s in records]
        # 更新数据
        c = update_relation(relation_list)
        return AllResponse.operate_successfully(msg='移除成功,移除数:{}'.format(c))

    elif product.action == 'time_add':  # 按时间添加
        if not product.time_start or not product.time_end:
            return AllResponse.validate_error(msg='时间段参数错误!')
        start_time = product.time_start + '00:00:00'
        end_time = product.time_end + '00:00:00'
        # 查询该段时间的所有策略
        sql = """SELECT id FROM info_strategy WHERE create_time>=%s AND create_time<=%s AND is_delete=0;"""
        records = db_conn.query(sql, param=(start_time, end_time), keep_conn=True)
        relation_list = [{'strategy_id': s['id'], 'product_id': product.product_id, 'is_active': 1} for s in records]
        # 更新数据，再插入数据
        c1 = update_relation(relation_list, keep_conn=True)
        c2 = insert_relation(relation_list)
        count = max(c1, c2)
        return AllResponse.operate_successfully(msg='添加成功,新增数:{}'.format(count))

    elif product.action == 'time_remove':  # 按时间移除
        if not product.time_start or not product.time_end:
            return AllResponse.validate_error(msg='时间段参数错误!')
        start_time = product.time_start + '00:00:00'
        end_time = product.time_end + '00:00:00'
        # 查询该段时间的所有策略
        sql = """SELECT id FROM info_strategy WHERE create_time>=%s AND create_time<=%s AND is_delete=0;"""
        records = db_conn.query(sql, param=(start_time, end_time), keep_conn=True)
        relation_list = [{'strategy_id': s['id'], 'product_id': product.product_id, 'is_active': 0} for s in records]
        # 更新数据
        c = update_relation(relation_list)
        return AllResponse.operate_successfully(msg='移除成功,移除数:{}'.format(c))

    elif product.action == 'author_time_add':  # 按作者和时间添加
        if not all([product.creator_id, product.time_start, product.time_end]):
            return AllResponse.validate_error()
        start_time = product.time_start + '00:00:00'
        end_time = product.time_end + '00:00:00'
        # 查询该段时间的指定作者策略
        sql = """SELECT id FROM info_strategy 
              WHERE creator=%s AND create_time>=%s AND create_time<=%s AND is_delete=0;"""
        records = db_conn.query(sql, param=(product.creator_id, start_time, end_time), keep_conn=True)
        relation_list = [{'strategy_id': s['id'], 'product_id': product.product_id, 'is_active': 1} for s in records]
        # 更新数据，再插入数据
        c1 = update_relation(relation_list, keep_conn=True)
        c2 = insert_relation(relation_list)
        count = max(c1, c2)
        return AllResponse.operate_successfully(msg='添加成功,新增数:{}'.format(count))

    elif product.action == 'author_time_remove':  # 按作者和时间移除
        if not all([product.creator_id, product.time_start, product.time_end]):
            return AllResponse.validate_error()
        start_time = product.time_start + '00:00:00'
        end_time = product.time_end + '00:00:00'
        # 查询该段时间的指定作者策略
        sql = """SELECT id FROM info_strategy 
              WHERE creator=%s AND create_time>=%s AND create_time<=%s AND is_delete=0;"""
        records = db_conn.query(sql, param=(product.creator_id, start_time, end_time), keep_conn=True)
        relation_list = [{'strategy_id': s['id'], 'product_id': product.product_id, 'is_active': 0} for s in records]
        # 更新数据
        c = update_relation(relation_list)
        return AllResponse.operate_successfully(msg='移除成功,移除数:{}'.format(c))

    elif product.action == 'target_add':  # 按目标添加
        if not product.strategy_id:
            return AllResponse.validate_error(msg='没有指定策略!')
        relation_list = [{'strategy_id': product.strategy_id, 'product_id': product.product_id, 'is_active': 1}]
        # 更新数据，再插入数据
        c1 = update_relation(relation_list, keep_conn=True)
        c2 = insert_relation(relation_list)
        count = max(c1, c2)
        return AllResponse.operate_successfully(msg='添加成功,新增数:{}'.format(count))

    elif product.action == 'target_remove':  # 按目标移除
        if not product.strategy_id:
            return AllResponse.validate_error(msg='没有指定策略!')
        relation_list = [{'strategy_id': product.strategy_id, 'product_id': product.product_id, 'is_active': 0}]
        # 更新数据
        c = update_relation(relation_list)
        return AllResponse.operate_successfully(msg='移除成功,移除数:{}'.format(c))

    else:
        pass
    return AllResponse.operate_successfully()


@work_strategy_api.get('/product/successRate/')  # 查询策略产品的成功率
def product_strategy_success_rate(p: int = Query(..., ge=1)):
    # 查询产品详情
    db_conn = FAConnection()
    product_sql = """
        SELECT name,fund FROM info_strategy_product WHERE id=%s;
    """
    product_obj = db_conn.query(product_sql, param=[p], fetchone=True, keep_conn=True)[0]
    if not product_obj:
        db_conn.close()
        return AllResponse.server_no_content(msg='该产品不存在!')

    # 查询产品内的策略数据
    sql = """
        SELECT SQL_CALC_FOUND_ROWS a.variety_en,a.create_time,a.close_time,a.profit,a.fund,a.creator,b.variety_name 
        FROM info_strategy As a INNER JOIN ruizy_variety As b ON a.variety_en=b.variety_en 
        INNER JOIN info_strategy_product_relation As c ON a.id=c.strategy_id 
        WHERE c.is_active=1 AND a.is_delete=0 AND c.product_id=%s 
        ORDER BY a.create_time;
    """
    records = db_conn.query(sql, param=(p,))
    df = pd.DataFrame(records)

    if df.empty:
        return AllResponse.server_no_content(msg='该产品没有策略成功率数据!')

    df['create_date'] = df['create_time'].apply(lambda x: x.strftime('%Y-%m-%d'))
    running_df = df[~pd.notnull(df['close_time'])]  # 运行中的策略
    df = df[pd.notnull(df['close_time'])]  # 结束的策略
    success_df = df[df['profit'].gt(0)]  # 成功的策略

    # 按日期计算成功数、累计数(已结束的才纳入计算)
    date_series = df.groupby(by=['create_date'])['create_date'].count()  # 按日期计数
    date_success_series = success_df.groupby(by=['create_date'])['create_date'].count()  # 按日期成功数
    date_df = pd.DataFrame({'create_date': date_series.index, 'count': date_series.values})
    date_df.sort_values(by='create_date')
    date_df['cum_count'] = date_df['count'].cumsum()  # 按日期的累计数
    date_success_df = pd.DataFrame({'create_date': date_success_series.index,
                                    'success_count': date_success_series.values})
    date_success_df.sort_values(by='create_date')
    date_success_df['cum_success_count'] = date_success_df['success_count'].cumsum()  # 按日期的累计成功数
    # 按日期计算收益数(已结束的才纳入计算)
    profit_date_df = df.groupby(by=['create_date'])['profit'].sum()  # 按日期计算收益
    profit_date_df = pd.DataFrame({'create_date': profit_date_df.index, 'profit': profit_date_df.values})
    profit_date_df.sort_values(by='create_date')
    profit_date_df['cum_profit'] = profit_date_df['profit'].cumsum()

    # 合并数据
    date_mix_df = pd.merge(date_df, date_success_df, on='create_date', how='left')  # 日期数和成功数
    date_mix_df = pd.merge(date_mix_df, profit_date_df, on='create_date', how='left')  # 合并收益额
    date_mix_df.fillna(method='ffill', inplace=True)
    date_mix_df['cum_profit'] = date_mix_df['cum_profit'].astype(float)
    # 计算累计收益率 = 日累计收益率 / 初始资金
    date_mix_df['cum_profit_rate'] = round(date_mix_df['cum_profit'] / product_obj['fund'], 4)
    # 计算累计成功率 = 日累计成功数 / 日累计数
    date_mix_df['cum_success_rate'] = round(date_mix_df['cum_success_count'] / date_mix_df['cum_count'], 4)
    # 当前累计收益额、策略总数、运行数
    m_row = date_mix_df[date_mix_df['create_date'] == date_mix_df['create_date'].max()]
    product_obj['cum_profit'] = date_mix_df.loc[m_row.index.values[0], 'cum_profit']
    product_obj['total_count'] = len(records)
    product_obj['running_count'] = running_df.shape[0]
    response_data = {
        'product': product_obj,
        'chart_data': date_mix_df[['create_date', 'cum_success_rate', 'cum_profit_rate']].to_dict(orient='records')
    }
    return AllResponse.operate_successfully(data=response_data)


@work_strategy_api.get('/statistics/')  # 按时间统计交易策略(数量按创建时间、收益率按结束时间)
def investment_plan_statistics(ruizy_token: str = Header(...), start: str = Query(...), end: str = Query(...),
                               st: int = Query(0, ge=0, le=1)):
    person = security.decrypt_access_token(ruizy_token)
    if not person:
        return AllResponse.no_authorization()

    start_date = datetime_utils.auth_datetime_string(datetime_string=start, f='%Y%m%d', rf='%Y-%m-%d')
    end_date = datetime_utils.auth_datetime_string(datetime_string=end, f='%Y%m%d', rf='%Y-%m-%d')
    if not all([start_date, end_date]):
        return AllResponse.validate_error(msg='日期查询格式错误!')

    is_admin = security.operator_is_manager(person)
    only_view_team = True if st else False
    members, is_leader = work_utils.get_operator_group_members(user_id=person['uid'], query_date=start_date)

    start_date += ' 00:00:00'
    end_date += ' 23:59:59'
    # 查询创建时间在区域内的数据
    sql = """
        SELECT a.create_time,a.id,a.creator,a.open_time,a.close_time,a.fund,a.profit,a.examine_state,b.admin_name   
        FROM info_strategy As a
        INNER JOIN ruizy_user As b ON a.creator=b.id 
        WHERE a.is_delete=0 AND a.create_time>=%s AND a.create_time<=%s;
    """
    prm = (start_date, end_date)
    db_conn = FAConnection()
    records = db_conn.query(sql, param=prm, keep_conn=True)  # 以创建时间为基准的数据

    # 查询结束时间在时间区域内的数据
    sql2 = """
        SELECT a.create_time,a.id,a.creator,a.open_time,a.close_time,a.fund,a.profit,a.examine_state,b.admin_name   
        FROM info_strategy As a
        INNER JOIN ruizy_user As b ON a.creator=b.id WHERE a.is_delete=0 AND a.close_time IS NOT NULL 
        AND a.close_time>=%s AND a.close_time<=%s;
    """
    prm2 = (start_date, end_date)
    finish_records = db_conn.query(sql2, param=prm2)  # 以结束时间为基准的数据

    # 查看的数据控制
    if is_admin:  # 管理员可以统计所有人或组员
        if only_view_team:  # 只查看组员
            members_id = [m['user_id'] for m in members]  # 组员id
            records = list(filter(lambda x: x['creator'] in members_id, records))
            finish_records = list(filter(lambda x: x['creator'] in members_id, finish_records))
    else:  # 不是管理员，只能查自己或组员
        members_id = [person['uid']]
        if only_view_team and is_leader:
            members_id = [m['user_id'] for m in members]  # 组员id
        records = list(filter(lambda x: x['creator'] in members_id, records))
        finish_records = list(filter(lambda x: x['creator'] in members_id, finish_records))

    user_dict = {}

    # 统计以数量(以创建时间为基准) examine_state=0的要计入失败量
    for plan in records:
        if plan['creator'] in user_dict.keys():
            user_data = user_dict[plan['creator']]
            user_data['total_count'] += 1
            if plan['close_time']:  # 策略结束
                user_data['finish_count'] += 1
            else:
                user_data['running_count'] += 1
        else:
            total_fund = 0
            total_profit = 0

            total_count = 1
            running_count = 1
            finish_count = 0
            if plan['close_time']:  # 策略结束
                running_count = 0
                finish_count = 1
            user_dict[plan['creator']] = {
                'user_id': plan['creator'],
                'admin_name': plan['admin_name'],
                'success_count': 0,
                'running_count': running_count,
                'finish_count': finish_count,
                'close_finish_count': 0,
                'total_count': total_count,
                'total_fund': total_fund,
                'total_profit': total_profit
            }

    # 统计成功率、收益率(以结束时间为基准)
    for plan in finish_records:
        if plan['creator'] in user_dict.keys():
            user_data = user_dict[plan['creator']]
            user_data['close_finish_count'] += 1
            user_data['total_fund'] += plan['fund']
            if plan['examine_state'] == 1:
                user_data['total_profit'] += plan['profit']
            if plan['profit'] > 0 and plan['examine_state'] == 1:
                user_data['success_count'] += 1
        else:
            running_count, success_count, total_profit = 0, 0, 0
            total_fund = plan['fund']
            if plan['examine_state'] == 1:  # 有效的参与计算收益率
                total_profit = plan['profit']
            if plan['profit'] > 0 and plan['examine_state'] == 1:  # examine_state=0的要计入失败量
                success_count = 1
            user_dict[plan['creator']] = {
                'user_id': plan['creator'],
                'admin_name': plan['admin_name'],
                'total_count': 0,
                'finish_count': 0,
                'running_count': running_count,  # 不存在的表示本期没有发布新策略，running_count=0
                'close_finish_count': 1,  # 计算成功率的
                'success_count': success_count,
                'total_fund': total_fund,
                'total_profit': total_profit
            }

    statistics_data = []
    for _, v in user_dict.items():
        v['total_profit_rate'] = 0
        v['success_rate'] = 0
        # 成功率
        if v['close_finish_count'] > 0:
            v['success_rate'] = round(v['success_count'] / v['close_finish_count'], 4)
        if v['total_fund'] > 0:
            v['total_profit_rate'] = round(v['total_profit'] / v['total_fund'], 4)
        statistics_data.append(v)
    statistics_data.sort(key=lambda x: x['total_count'], reverse=True)

    if len(statistics_data) > 0:
        # 合计
        t_running_count = sum([st['running_count'] for st in statistics_data])
        t_finish_count = sum([st['finish_count'] for st in statistics_data])
        t_success_count = sum([st['success_count'] for st in statistics_data])
        t_total_fund = sum([st['total_fund'] for st in statistics_data])
        t_total_profit = sum([st['total_profit'] for st in statistics_data])
        statistics_data.append({
            'user_id': -1,
            'admin_name': '合计',
            'total_count': len(records),  # 创建日期为基准
            'running_count': t_running_count,  # 创建日期为基准
            'success_count': t_success_count,  # 结束日期为基准的成功数
            'finish_count': t_finish_count,  # 创建日期为基准的结束数
            'close_finish_count': len(finish_records),  # 结束日期为基准
            'total_fund': '%.0f万' % (t_total_fund / 10000),
            'total_profit': t_total_profit,  # 结束日期为基准
            'success_rate': round(t_success_count / len(finish_records), 4) if len(finish_records) > 0 else 0,
            # 结束日期为基准
            'total_profit_rate': round(t_total_profit / t_total_fund, 4) if t_total_fund > 0 else 0  # 结束日期为基准
        })
    return AllResponse.operate_successfully(data=statistics_data)


@work_strategy_api.get('/statisticsMonthly/')  # 按月份统计策略数量
def statistics_strategy_monthly(ruizy_token: str = Header(...), start: str = Query(...), end: str = Query(...),
                                st: int = Query(1, ge=1, le=2)):
    person = security.decrypt_access_token(ruizy_token)
    if not person:
        return AllResponse.no_authorization()

    start_date = datetime_utils.auth_datetime_string(datetime_string=start, f='%Y%m%d', rf='%Y-%m-%d')
    end_date = datetime_utils.auth_datetime_string(datetime_string=end, f='%Y%m%d', rf='%Y-%m-%d')
    if not all([start_date, end_date]):
        return AllResponse.validate_error(msg='日期查询格式错误!')

    is_admin = security.operator_is_manager(person)
    members, is_leader = work_utils.get_operator_group_members(user_id=person['uid'], query_date=start_date)
    start_date += ' 00:00:00'
    end_date += ' 23:59:59'
    db_conn = FAConnection()
    # 查询创建时间在时间区域内的数据
    sql = """
        SELECT a.create_time,a.id,a.creator,a.open_time,a.close_time,a.fund,a.profit,a.examine_state,b.admin_name   
        FROM info_strategy As a
        INNER JOIN ruizy_user As b ON a.creator=b.id WHERE a.is_delete=0 AND a.create_time>=%s AND a.create_time<=%s;
    """
    prm = (start_date, end_date)
    finish_records = db_conn.query(sql, param=prm)  # 以结束时间为基准的数据
    # 查看的数据控制
    if is_admin:  # 管理员可以统计所有人或组员
        if st == 2:  # 只查看组员
            members_id = [m['user_id'] for m in members]  # 组员id
            finish_records = list(filter(lambda x: x['creator'] in members_id, finish_records))
    else:  # 不是管理员，只能查自己或组员
        members_id = [person['uid']]
        if st == 2 and is_leader:
            members_id = [m['user_id'] for m in members]  # 组员id
        finish_records = list(filter(lambda x: x['creator'] in members_id, finish_records))

    # 对数据按人员，按月份进行统计
    # user_names = {}
    # user_data = {}
    # for row in finish_records:
    #     row['month'] = row['create_time'].strftime('%Y%m')
    #     if row['creator'] in user_data.keys():
    #         user_data[row['creator']].append(row)
    #     else:
    #         user_data[row['creator']] = [row]
    #     user_names[row['creator']] = row['admin_name']
    # 获取查询时间各个月份
    month_list = datetime_utils.month_of_range(start=start_date, end=end_date, ft='%Y-%m-%d %H:%M:%S')

    # statistics_data = []
    # # 对各个useData数据进行月份统计{month:0,count:0,running:0}
    # for uid, udata in user_data.items():
    #     udata.sort(key=lambda x: x['month'])
    #     month_data = {}
    #     total_count, total_running_count = 0, 0
    #     for m, mdata in itertools.groupby(udata, key=lambda x: x['month']):
    #         month_objs = list(mdata)
    #         month_count = len(month_objs)
    #         month_running_count = len(list(filter(lambda i: not i['close_time'], month_objs)))
    #         month_data[f'count_{m}'] = month_count
    #         month_data[f'running_{m}'] = month_running_count
    #     for month in month_list:
    #         month_count = month_data.get(f'count_{month}')
    #         month_running_count = month_data.get(f'running_{month}')
    #         show_text = ''
    #         if month_count:
    #             show_text += str(month_count)
    #             total_count += month_count
    #         if month_running_count:
    #             show_text += f' ({month_running_count})'
    #             total_running_count += month_running_count
    #         month_data[month] = show_text
    #     total_show_text = ''
    #     if total_count:
    #         total_show_text += str(total_count)
    #     if total_running_count:
    #         total_show_text += f' ({total_running_count})'
    #     statistics_data.append({
    #         'user_id': uid,
    #         'admin_name': user_names[uid],
    #         'total_count': total_count,
    #         'total_running': total_running_count,
    #         'total_show_text': total_show_text,
    #         **month_data.copy()
    #     })
    #
    # # 增加合计数
    # table_columns = []
    # sum_obj = {
    #     'user_id': -1,
    #     'admin_name': '合计',
    #     'total_count': 0,
    #     'total_running': 0,
    #     'total_show_text': ''
    #
    # }
    # tt, ttn = 0, 0  # 月份的所有合计
    # for m in month_list:
    #     table_columns.append({'prop': m, 'label': m})
    #     total_month, total_month_running = 0,  0
    #     for u in statistics_data:
    #         total_month += u.get(f'count_{m}', 0)   # 所有人的当月总计
    #         total_month_running += u.get(f'running_{m}', 0)  # 所有人的当月运行总计
    #     sum_obj[f'count_{m}'] = total_month
    #     sum_obj[f'running_{m}'] = total_month_running
    #
    #     t_show = ''
    #     if total_month:
    #         t_show += str(total_month)
    #     if total_month_running:
    #         t_show += f' ({total_month_running})'
    #     sum_obj[m] = t_show
    #
    #     tt += total_month
    #     ttn += total_month_running
    # tt_show_text = ''
    # if tt:
    #     tt_show_text += str(tt)
    # if ttn:
    #     tt_show_text += f' ({ttn})'
    # sum_obj['total_show_text'] = tt_show_text
    #
    # statistics_data.append(sum_obj)  # 将合计数加入行
    #
    # table_columns.insert(0, {'prop': 'admin_name', 'label': '姓名'})
    # table_columns.append({'prop': 'total_show_text', 'label': '小计'})
    statistics_data, table_columns = work_utils.statistics_monthly(finish_records, month_list=month_list,
                                                                   running_count=True, flag=True)
    response_data = {
        'statistics_data': statistics_data,
        'table_columns': table_columns
    }
    return AllResponse.operate_successfully(data=response_data)


@work_strategy_api.get('/statisticsChart/')  # 查询交易策略的收益率，成功率图形配置（时间基础为结束时间，运行中不统计）
def investment_statistics_chart(ruizy_token: str = Header(...), start: str = Query(...), end: str = Query(...),
                                user: int = Query(0, ge=0)):
    person = security.decrypt_access_token(ruizy_token)
    if not person:
        return AllResponse.no_authorization()

    start_date = datetime_utils.auth_datetime_string(datetime_string=start, f='%Y%m%d', rf='%Y-%m-%d')
    end_date = datetime_utils.auth_datetime_string(datetime_string=end, f='%Y%m%d', rf='%Y-%m-%d')
    if not all([start_date, end_date]):
        return AllResponse.validate_error(msg='日期查询格式错误!')
    start_date += ' 00:00:00'
    end_date += ' 23:59:59'
    user_id = 'NOUSER' if not user else user
    # 查询结束时间在时间区域内的数据
    sql2 = """
            SELECT a.create_time,a.id,a.creator,a.open_time,a.close_time,a.fund,a.profit,b.admin_name   
            FROM info_strategy As a
            INNER JOIN ruizy_user As b ON a.creator=b.id WHERE a.close_time IS NOT NULL 
            AND a.close_time>=%s AND a.close_time<=%s AND IF('NOUSER'=%s,TRUE,a.creator=%s);
        """
    prm2 = (start_date, end_date, user_id, user_id)
    db_conn = FAConnection()
    finish_records = db_conn.query(sql2, param=prm2)
    # 进行按日期的数据统计
    df = pd.DataFrame(finish_records)
    if df.empty:
        return AllResponse.operate_successfully()
    df['close_date'] = df['close_time'].apply(lambda x: x.strftime('%Y%m%d'))
    df['fund'] = df['fund'].astype(float)
    df['profit'] = df['profit'].astype(float)
    count_df = df.groupby(by='close_date', as_index=False)['id'].count()
    count_df.rename(columns={'id': 'total_count'}, inplace=True)
    success_df = df[df['profit'] > 0].groupby(by='close_date', as_index=False)['id'].count()
    success_df.rename(columns={'id': 'success_count'}, inplace=True)
    # 按日期统计资金、收益的和
    profit_df = df.groupby(by='close_date', as_index=False)[['fund', 'profit']].sum()
    profit_df = pd.merge(profit_df, count_df, how='outer')  # 合并总数量
    profit_df = pd.merge(profit_df, success_df, how='outer')  # 合并成功数
    profit_df.fillna(0, inplace=True)
    profit_df.sort_values(by='close_date', inplace=True)
    profit_df['fund_cum'] = profit_df['fund'].cumsum()
    profit_df['profit_cum'] = profit_df['profit'].cumsum()
    profit_df['total_count_cum'] = profit_df['total_count'].cumsum()
    profit_df['success_count_cum'] = profit_df['success_count'].cumsum()
    # 当日的前端没有启用，不进行计算
    # profit_df['current_profit_rate'] = (profit_df['profit'] / profit_df['fund']).round(4)  # 当日收益率
    profit_df['cum_profit_rate'] = (profit_df['profit_cum'] / profit_df['fund_cum']).round(4)  # 累计收益率
    # profit_df['current_success_rate'] = (profit_df['success_count'] / profit_df['total_count']).round(4)  # 当日成功率
    profit_df['cum_success_rate'] = (profit_df['success_count_cum'] / profit_df['total_count_cum']).round(4)  # 累计成功率
    # print(profit_df)
    return AllResponse.operate_successfully(data=profit_df.to_dict(orient='records'))


@work_strategy_api.get('/forCheck/')  # 按日期查询交易策略为了检查
def query_strategy_for_check(start: str = Query(...), end: str = Query(...)):
    start_time = datetime_utils.auth_datetime_string(start, f='%Y%m%d', rf='%Y-%m-%d 00:00:00')
    end_time = datetime_utils.auth_datetime_string(end, f='%Y%m%d', rf='%Y%m%d 23:59:59')
    if not all([start_time, end_time]):
        return AllResponse.validate_error(msg='查询日期格式错误！')
    db_conn = FAConnection()
    sql = "SELECT * FROM info_strategy WHERE is_delete=0 AND create_time>=%s AND create_time<=%s;"
    records = db_conn.query(sql, param=[start_time, end_time])
    return AllResponse.operate_successfully(data=records)


@work_strategy_api.put('/forCheck/')  # 检查后修改策略数据
def machine_modify_strategy(body_data: dict = Body(...)):
    strategy_id = body_data.get('id')
    if not strategy_id:
        return AllResponse.validate_error(msg='参数错误')
    body_data['checker'] = '机器人'
    body_data = dbutils.exclude_columns(body_data, includes=['close_time', 'close_way', 'checker'])
    middle_sql = dbutils.create_sql(body_data)
    sql = 'UPDATE info_strategy SET ' + middle_sql + 'WHERE id=%(strategy_id)s LIMIT 1;'
    body_data['strategy_id'] = strategy_id
    db_conn = FAConnection()
    _, success = db_conn.execute(sql, param=body_data)
    return AllResponse.operate_successfully() if success else AllResponse.server_error()
